<html>
	<head> <title> Players </title> </head>
	<body>
		<h1>Players</h1>
		<?php
			$db = mysql_connect('oniddb.cws.oregonstate.edu','rundej-db','Hp2tYI0CxEY4U9J9');
			if(mysql_errno($db)){
				echo mysql_errno($db);
			}
			mysql_select_db('rundej-db',$db);
			
			$query = "select * from Players natural join Region";
			//selection process here
			if($_POST["runCount"] || $_POST["wicketCount"] || $_POST["moreRuns"] || $_POST["runRat"] || $_POST["wicketRat"])
			{
				$befores = 0;
				$query.=" where";
				if($_POST["wicketCount"])
				{
					$query.=" Wickets_Taken > ".$_POST["wicketCount"];
					$befores += 1;				
				}
				if($_POST["runCount"])
				{
					if($befores > 0)
						$query.=" and";
					$query .=" Runs_Scored > ".$_POST["runCount"];
					$befores += 1;				
				}
				if($_POST["runRat"])
				{
					if($befores > 0)
						$query .= " and";
					$query .= " Runs_Scored/Innings_Batted > ".$_POST["runRat"];
					$befores += 1;
				}
				if($_POST["wicketRat"])
				{
					if($befores > 0)
						$query .= " and";
					$query .= " Wickets_Taken/Overs_Bowled > ".$_POST["wicketRat"];
					$befores += 1;
				}
				if($_POST["moreRuns"])
				{
					$query = "SELECT distinct s1.* FROM (Players s1 natural join Region r1), (Players s2 natural join Region r2) WHERE s1.Runs_Scored > s2.Runs_Scored + ".$_POST["moreRuns"]." and r2.Num_Wins > r1.Num_Wins";
				}
			}
			//echo $query;
			$result = mysql_query($query,$db);
			$numResults = mysql_num_rows($result);
		?>	
			<table border = "5">
				<tr>
					<th>Number</th>
					<th>Fname</th>
					<th>Lname</th>
					<th>Region Name</th>
					<th>Position</th>
					<th>Innings Batted</th>
					<th>Overs Bowled</th>
					<th>Wickets Taken</th>
					<th>Runs Scored</th>
				</tr>
		<?php
			$c;
			$row;
			for($c = 0; $c < $numResults; $c++)
			{
				?><tr><?php
				$row = mysql_fetch_array($result);
				?><td><?php
				echo $row["Number"];
				?></td><td><?php
				echo $row["Fname"];
				?></td><td><?php
				echo $row["Lname"];
				?></td><td><?php
				echo $row["Region_Name"];
				?></td><td><?php
				echo $row["Primary_Position"];
				?></td><td><?php
				echo $row["Innings_Batted"];
				?></td><td><?php
				echo $row["Overs_Bowled"];
				?></td><td><?php
				echo $row["Wickets_Taken"];
				?></td><td><?php
				echo $row["Runs_Scored"];			
				?></td></tr><?php
			}
			mysql_free_result($result);
			mysql_close($db);
		?>
		</table>
		<a href="players.php">Show All Players</a>
		<h2>Show Players that have...</h2>
		<form action = "players.php" method = "post">
			Taken more than <input type = "text" name = "wicketCount"/> Wickets </br>
			Scored more than <input type = "text" name = "runCount"/> Runs </br>
			Have runs/innings better than <input type = "text" name = "runRat"/> </br>
			Have wickets/overs better than <input type = "text" name = "wicketRat"/> </br>
			<b>OR</b></br>
			Scored <input type = "text" name = "moreRuns"/> more runs than a player with more wins than them </br>
			<input type = "submit" value = "Submit" />
		</form>
		<h2>Add Player</h2>
		<form action = "addPlayer.php" method = "post">
			First Name: <input type = "text" name = "playerFName"/> <br/>
			Last Name: <input type = "text" name = "playerLName"/> <br/>
			Region #: <input type = "text" name = "playerRegion"/> <br/>
			ID: <input type = "text" name = "playerID"/> <br/>
			<input type = "submit" value = "Submit" />
		</form>
		<h2>Update Player Stats</h2>
		<form action = "updatePlayer.php" method = "post">
			ID: <input type = "text" name = "playerID"/> <br/>
			Runs Scored: <input type = "text" name = "deltaRuns"/> <br/>
			Wickets Taken: <input type = "text" name = "deltaWickets"/> <br/>
			Overs Bowled: <input type = "text" name = "deltaOvers"/> <br/>
			Innings Batted: <input type = "text" name = "deltaInnings"/> </br>
			New Position: <input type = "text" name = "position"/> </br>
			New Region: <input type = "text" name = "region"/> </br>
			<input type = "submit" value = "Submit" />
		</form>
		
		<center>
			<a href = "index.php">Home</a>	
			<a href = "umpires.php">Umpires</a>	
			<a href = "regions.php">Regions</a>
		</center>	
		
	</body>

</html>